import sqlite3
import connector.DatabaseDriver
from connector.DatabaseDriver import DatabaseDriver


def get_all_info(driver: DatabaseDriver) -> tuple:
    results: tuple = driver.select_all('select * from information')
    return results


def get_info_by_name_room_tele(driver: DatabaseDriver, host_name: str = '', room_num: str = '', tele_num: str = '') -> tuple:
    if host_name == '' and room_num == '' and tele_num == '':
        return get_all_info(driver)
    else:
        if host_name != '' and room_num != '' and tele_num != '':
            sql: str = 'select * from information where host_name like "%{}%" and room_num like "%{}%" and tele_num={}'.format(host_name, room_num, tele_num)
        elif host_name != '' and room_num != '':
            sql: str = 'select * from information where host_name like "%{}%" and room_num like "%{}%"'.format(host_name, room_num)
        elif host_name != '' and tele_num != '':
            sql: str = 'select * from information where host_name like "%{}%" and tele_num={}'.format(host_name, tele_num)
        elif room_num != '' and tele_num != '':
            sql: str = 'select * from information where  room_num like "%{}%" and tele_num={}'.format(room_num, tele_num)
        elif host_name != '':
            sql: str = 'select * from information where host_name like "%{}%"'.format(host_name)
        elif room_num != '':
            sql: str = 'select * from information where room_num like "%{}%"'.format(room_num)
        elif tele_num != '':
            sql: str = 'select * from information where tele_num={}'.format(tele_num)
        return driver.select_all(sql=sql)



# 插入新住户
def insert_info(driver: DatabaseDriver, hostname: str, ages: int, telenum: str, idnum: str, sexs: str,
                roomnum: str) -> int:
    return driver.insert(
        sql='insert into information(host_name,age, tele_num, id_num, sex, room_num) values ("{}",{},"{}","{}","{}",'
            '"{}")'.format(hostname, ages, telenum, idnum, sexs, roomnum))


# 删除住户
def delete_info(driver: DatabaseDriver, id_num: str) -> int:
    return driver.delete('delete from information where id_num={}'.format(id_num))


# 获取所有住户信息
def get_pay_info(driver: DatabaseDriver) -> tuple:
    return driver.select_all('select * from information where water_pay !=0 or electronic_pay !=0 or server_pay !=0')


def update_pay_info(driver: DatabaseDriver, roomnum: str) -> int:
    return driver.update('UPDATE information SET water_pay = 0, electronic_pay = 0, server_pay = 0 WHERE room_num = '
                         '"{}"'.format(roomnum))


def complain_info(driver: DatabaseDriver) -> tuple:
    return driver.select_all('select * from information where complain_text is not null')

def update_complain_info(driver: DatabaseDriver, roomnum: str) -> int:
    return driver.update('UPDATE information SET complain_text = ""WHERE room_num = "{}"'.format(roomnum))


